package com.xxl.job.executor.service.impl;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.ICountStationMinistryService;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;

@Service("countStationMinistryService")
public class CountStationMinistryService implements ICountStationMinistryService {
    Connection connPre = null;

    @Override
    public void transfer() throws Exception {
        XxlJobLogger.log("=====开始执行业务操作===");
        final String recordMonitor = "tb_datauploadstatus";
        final String recordDetail = "tb_uploadfaillist";
        try {
            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
            connPre = DataSourceUtils.getConnection(dataSourcePre);
            connPre.setAutoCommit(false);

            String recordStrMonitor = DBUtils.getCurSynRecordNum(connPre, recordMonitor);
            XxlJobLogger.log("[站部监视]本次门架流水最大记录RECORDID为:" + recordStrMonitor);

            String recordStrDetail = DBUtils.getCurSynRecordNum(connPre, recordDetail);
            XxlJobLogger.log("[异常明细]本次门架站级统计最大记录RECORDID为:" + recordStrDetail);

            String dbCurTime = DBUtils.getDBCurTime(connPre);
            XxlJobLogger.log("[普通]本次同步截止时间为:" + dbCurTime);

            StringBuffer sql = new StringBuffer();
            sql.append("insert into TB_SYNC_MINISTRY_MONITOR(table_name,trade_date,trade_day,trade_hour,station_id,squad_no,total_cnt,upload_cnt,notsend_cnt,insert_time) " +
                    "select tablename,statdayhour,statday,stathour,nvl(to_char(c.station_id),stationid),shift,uploadcnt,total,sendcnt,sysdate " +
                    "from TB_DATAUPLOADSTATUS a " +
                    "left join (select station_hex,min(station_id) station_id from tb_charge_station group by station_hex) c on a.stationid=c.station_hex " +
                    "where STATIONID is not null and inserttabletime>=to_date('").append(recordStrMonitor).append("','yyyy-mm-dd hh24:mi:ss') and inserttabletime<to_date('").append(dbCurTime).append("','yyyy-mm-dd hh24:mi:ss') ");
            Integer count = DBUtils.execute(connPre, sql.toString());
            XxlJobLogger.log("[站部监视]本次门架流水同步到临时表(条):" + count);

            count = DBUtils.modifySynRecordNum(connPre, recordMonitor, recordStrMonitor, dbCurTime);
            if (count == null || count != 1) {
                XxlJobLogger.log("[站部监视异常]更新记录时间异常");
                throw new Exception("[站部监视异常]更新记录时间异常");
            }
            XxlJobLogger.log("[站部监视]更新记录时间为:" + dbCurTime);

            String sqlPro = "insert into TB_SYNC_EXCEPTION_DETAIL_TMP(" +
                    "table_name,list_no,send_time,station_id,lane_no,err_code,err_info,station_ip,tjdate) " +
                    "select tablename,id,optime,nvl(to_char(c.station_id),stationid),b.lane_no,errorcode,errmsg,stationip,TO_CHAR(optime,'YYYYMMDDHH24') " +
                    "from tb_uploadfaillist a " +
                    "left join (select lane_hex,min(lane_no) lane_no from tb_charge_lane group by lane_hex) b on a.laneno=b.lane_hex " +
                    "left join (select station_hex,min(station_id) station_id from tb_charge_station group by station_hex) c on a.stationid=c.station_hex  " +
                    "where STATIONID is not null and inserttabletime>=to_date('" + recordStrDetail + "','yyyy-mm-dd hh24:mi:ss') and inserttabletime<to_date('" + dbCurTime + "','yyyy-mm-dd hh24:mi:ss') ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[异常明细]本次共同步异常明细(条):" + count);

            count = DBUtils.modifySynRecordNum(connPre, recordDetail, recordStrDetail, dbCurTime);
            if (count == null || count != 1) {
                XxlJobLogger.log("[异常明细异常]更新记录时间异常");
                throw new Exception("[异常明细异常]更新记录时间异常");
            }
            XxlJobLogger.log("[异常明细]更新记录时间为:" + dbCurTime);

            //按流水类型以及每天小时统计异常条数
            sqlPro = "MERGE INTO TB_STATIS_EXCEPTION_DETAIL A " +
                    "USING (" +
                    "SELECT TABLE_NAME,SUBSTR(tjdate,1,8) TRADE_DAY," +
                    "SUBSTR(tjdate,9,2) TRADE_HOUR," +
                    "STATION_ID,COUNT(1) EXCEPT_CNT,min(STATION_IP) STATION_IP " +
                    "FROM TB_SYNC_EXCEPTION_DETAIL_TMP " +
                    "GROUP BY TABLE_NAME,STATION_ID,tjdate " +
                    ") B " +
                    "ON (A.TABLE_NAME=B.TABLE_NAME AND A.STATION_ID=B.STATION_ID AND A.TRADE_DAY=B.TRADE_DAY AND A.TRADE_HOUR=B.TRADE_HOUR) " +
                    "WHEN MATCHED THEN " +
                    "  UPDATE SET A.EXCEPT_CNT=A.EXCEPT_CNT+B.EXCEPT_CNT,A.UPDATE_TIME=SYSDATE " +
                    "WHEN NOT MATCHED THEN " +
                    "  INSERT(TABLE_NAME,TRADE_DAY,TRADE_HOUR,STATION_ID,EXCEPT_CNT,STATION_IP,INSERT_TIME,UPDATE_TIME) " +
                    "  VALUES(B.TABLE_NAME,B.TRADE_DAY,B.TRADE_HOUR,B.STATION_ID,B.EXCEPT_CNT,B.STATION_IP,SYSDATE,SYSDATE) ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[异常明细]按流水类型以及每天小时统计异常条数(条):" + count);

            sqlPro = "insert into TB_SYNC_EXCEPTION_DETAIL(" +
                    "table_name,list_no,send_time,station_id,lane_no,err_code,err_info,station_ip,insert_time) " +
                    "select table_name,list_no,send_time,station_id,lane_no,err_code,err_info,station_ip,sysdate " +
                    "from TB_SYNC_EXCEPTION_DETAIL_TMP a ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[异常明细]本次共完成异常明细同步(条):" + count);

            //最后清除临时表数据
            sqlPro = "delete from TB_SYNC_EXCEPTION_DETAIL_TMP";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[异常明细]清除临时表(条):"+count);

            String strHour = dbCurTime.substring(11,13);
            if (strHour.equals("03")) { //凌晨3点执行一次删除7日前的数据
                String strCurDay = dbCurTime.substring(0,10);
                final String recordDelDetail = "del_tb_sync_exception_detail";
                String recordStrDelDetail = DBUtils.getCurSynRecordNum(connPre, recordDelDetail);
                XxlJobLogger.log("[异常明细]本次异常明细表7天前数据RECORDID为:" + recordStrDelDetail);
                if (recordStrDelDetail != null && strCurDay.compareTo(recordStrDelDetail) > 0) {
                    sqlPro = "delete from TB_SYNC_EXCEPTION_DETAIL where insert_time<sysdate-interval'7'day ";
                    count = DBUtils.execute(connPre, sqlPro);
                    XxlJobLogger.log("[异常明细]本次删除异常明细表7天前数据(条):"+count);

                    count = DBUtils.modifySynRecordNum(connPre, recordDelDetail, recordStrDelDetail, strCurDay);
                    if (count == null || count != 1) {
                        XxlJobLogger.log("[异常明细异常]更新7日前数据清除日期异常");
                        throw new Exception("[异常明细异常]更新7日前数据清除日期异常");
                    }
                    XxlJobLogger.log("[异常明细]更新7日前数据清除日期为:" + strCurDay);

                    //7天凌晨执行一次重新分析统计信息
                    Integer nDay = Integer.parseInt(dbCurTime.substring(8,10));
                    if (nDay % 7 == 1) {
                        sqlPro = "analyze table TB_SYNC_EXCEPTION_DETAIL compute statistics ";
                        count = DBUtils.execute(connPre, sqlPro);
                        XxlJobLogger.log("[异常明细]异常明细表重新分析统计信息(条):"+count);
                    }
                }
            }

            connPre.commit();

            sqlPro = "truncate table TB_SYNC_EXCEPTION_DETAIL_TMP";
            DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[普通]重置临时表成功");

            connPre.commit();

            XxlJobLogger.log("=====完成执行业务操作===");
        }catch(Exception e){
            if (connPre != null) {
                try {
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            XxlJobLogger.log("抛出异常："+e.getMessage());
            throw e;
        }finally{
            if (connPre != null) {
                try {
                    connPre.setAutoCommit(true);
                    connPre.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }
}
